<?php

class ReportsController extends Zend_Controller_Action
{
    function preDispatch()
    {
		if(!$_SESSION['identity'])
        {
            $this->_redirect("/login");
        }
		
         //Make menu
         $mod_menu = new Db_Tables2_BDictMenu();
         $this->view->main_menu = $mod_menu->MenuDecolation();
        
        $mod_params = new Db_Tables2_BDictParams();
        $this->view->sys_name = $mod_params->GetSysName();
    }
	
    function indexAction()
    {
		$params = $this->_request->getParams();
		
		$this->view->title = "在线合同看板";
		
		$mod_t_area = new Db_Tables2_TArea();
		$this->view->provinces = $mod_t_area->GetProvinces();
		
		$mod_params = new Db_Tables2_BDictParams();
		$sales_role_id = $mod_params->GetVal("LEVEL-1");
		
		$mod_user = new Db_Tables2_User();
		$this->view->sales = $mod_user->GetUsersByRole($sales_role_id);
		
		$mod_owners = new Db_Tables2_BOwner();
		$this->view->owners = $mod_owners->GetOwners();
		
		$mod_products = new Db_Tables2_BDictProduct();
		$this->view->products = $mod_products->GetProducts();
		
		$params_pool = array();
		$mod_tables = new Db_Joins_TablesJoins();
    	
		if($params['contract_start_date_start'])
    	{
    		$mod_tables->contract_start_date_start = $params['contract_start_date_start'];
    		$this->view->contract_start_date_start = $params['contract_start_date_start'];
    		$params_pool[] = "contract_start_date_start=".$params['contract_start_date_start'];
    	}
    	
		if($params['contract_start_date_end'])
    	{
    		$mod_tables->contract_start_date_end = $params['contract_start_date_end'];
    		$this->view->contract_start_date_end = $params['contract_start_date_end'];
    		$params_pool[] = "contract_start_date_end=".$params['contract_start_date_end'];
    	}
		
		if($params['contract_expired_date_start'])
    	{
    		$mod_tables->contract_expired_date_start = $params['contract_expired_date_start'];
    		$this->view->contract_expired_date_start = $params['contract_expired_date_start'];
    		$params_pool[] = "contract_expired_date_start=".$params['contract_expired_date_start'];
    	}
		
		if($params['contract_expired_date_end'])
    	{
    		$mod_tables->contract_expired_date_end = $params['contract_expired_date_end'];
    		$this->view->contract_expired_date_end = $params['contract_expired_date_end'];
    		$params_pool[] = "contract_expired_date_end=".$params['contract_expired_date_end'];
    	}
		
		if($params['service_start_date_start'])
    	{
    		$mod_tables->service_start_date_start = $params['service_start_date_start'];
    		$this->view->service_start_date_start = $params['service_start_date_start'];
    		$params_pool[] = "service_start_date_start=".$params['service_start_date_start'];
    	}
		
		if($params['service_start_date_end'])
    	{
    		$mod_tables->service_start_date_end = $params['service_start_date_end'];
    		$this->view->service_start_date_end = $params['service_start_date_end'];
    		$params_pool[] = "service_start_date_end=".$params['service_start_date_end'];
    	}
		
		if($params['service_expired_date_start'])
    	{
    		$mod_tables->service_expired_date_start = $params['service_expired_date_start'];
    		$this->view->service_expired_date_start = $params['service_expired_date_start'];
    		$params_pool[] = "service_expired_date_start=".$params['service_expired_date_start'];
    	}
		
		if($params['service_expired_date_end'])
    	{
    		$mod_tables->service_expired_date_end = $params['service_expired_date_end'];
    		$this->view->service_expired_date_end = $params['service_expired_date_end'];
    		$params_pool[] = "service_expired_date_end=".$params['service_expired_date_end'];
    	}
		
		if($params['sales_emp_id'] && "LEVEL-1" != $_SESSION['identity']['level'])
    	{
    		$mod_tables->sales_emp_id = $params['sales_emp_id'];
    		$this->view->sales_emp_id = $params['sales_emp_id'];
    		$params_pool[] = "sales_emp_id=".$params['sales_emp_id'];
    	}elseif("LEVEL-1" == $_SESSION['identity']['level']) //销售只可以看自己
		{
			$mod_tables->sales_emp_id = $_SESSION['identity']['userid'];
		}
		
		if($params['owner_id'])
    	{
    		$mod_tables->owner_id = $params['owner_id'];
    		$this->view->owner_id = $params['owner_id'];
    		$params_pool[] = "owner_id=".$params['owner_id'];
    	}
		
		if($params['dealer_type'])
    	{
    		$mod_tables->dealer_type = $params['dealer_type'];
    		$this->view->dealer_type = $params['dealer_type'];
    		$params_pool[] = "dealer_type=".$params['dealer_type'];
    	}
		
		if($params['contract_type'])
    	{
    		$mod_tables->contract_type = $params['contract_type'];
    		$this->view->contract_type = $params['contract_type'];
    		$params_pool[] = "contract_type=".$params['contract_type'];
    	}
		
		if($params['renew_type'])
    	{
    		$mod_tables->renew_type = $params['renew_type'];
    		$this->view->renew_type = $params['renew_type'];
    		$params_pool[] = "renew_type=".$params['renew_type'];
    	}
		
		if($params['product_id'])
    	{
    		$mod_tables->product_id = $params['product_id'];
    		$this->view->product_id = $params['product_id'];
    		$params_pool[] = "product_id=".$params['product_id'];
    	}
		
    	if($params['provinceId'])
    	{
    		$mod_tables->provinceId = $params['provinceId'];
    		$this->view->provinceId = $params['provinceId'];
    		$params_pool[] = "provinceId=".$params['provinceId'];
			
			$this->view->init_cities = $mod_t_area->GetCities($params['provinceId']);
    	}
    	
    	if($params['cityId'])
    	{
    		$mod_tables->cityId = $params['cityId'];
    		$this->view->cityId = $params['cityId'];
    		$params_pool[] = "cityId=".$params['cityId'];
    	}
		
		$mod_tables->status = 2; //只看在线订单
		$mod_tables->skip_pagination = 1; //取消分页
		
		if($params['keyword'])
    	{
    		$mod_tables->keyword = $params['keyword'];
    		$this->view->keyword = $params['keyword'];
    		$params_pool[] = "keyword=".$params['keyword'];
    	}
		
		$this->view->data = $result = $mod_tables->GetContracts();
		
		$mod_reports = new Db_Joins_ReportsJoins();
		$this->view->stat = $mod_reports->MakeStatForReport($result);
	}
	
    function hxbStatusAction()
    {
		$params = $this->_request->getParams();
		
		$this->view->title = "汇销宝状态";
		
		$no_auth = 0;
		
		$dealers_db = Zend_Registry::get("dealers_db");
		$select = $dealers_db->select();
		$select->from("b_bu_auth", "*");
		$select->where("emp_id = ?", $_SESSION['identity']['userid']);
		$row = $dealers_db->fetchRow($select);
		
		if($row)
		{
			$this->view->my_group_id_arr = explode("|", $row['group_id_txt']);
			$this->view->my_factory_id_arr = explode("|", $row['factory_id_txt']);
			$my_city_id_arr = explode("|", $row['city_id_txt']);
			
			if(!$my_city_id_arr[0])
			{
				$my_city_id_arr = NULL;
			}
		}else{
			$no_auth = 1;
		}
		
		$mod_group = new Db_Tables2_TDealerGroup();
		$this->view->groups = $mod_group->GetGroups();
		
		$mod_factory = new Db_Tables2_TFactory();
		$this->view->factories = $mod_factory->GetFactories();
		
		$mod_t_area = new Db_Tables2_TArea();
		$this->view->provinces = $mod_t_area->GetProvinces($my_city_id_arr);
		
		$params_pool = array();
		$mod_reports = new Db_Joins_ReportsJoins();
		$mod_reports->emp_auth_row = $row;
    	
		if($params['date_start'])
		{
			$date_start = $params['date_start'];
		}else{
			$date_start = date("Y-m-d", mktime(1,1,1,date("m"),date("d")-1,date("Y")));
		}
    	
		if($params['date_end'])
		{
			$date_end = $params['date_end'];
		}else{
			$date_end = date("Y-m-d", mktime(1,1,1,date("m"),date("d")-1,date("Y")));
		}
		
		if($date_start)
    	{
    		$mod_reports->date_start = $date_start;
    		$this->view->date_start = $date_start;
    		$params_pool[] = "date_start=".$date_start;
    	}
		
		if($date_end)
    	{
    		$mod_reports->date_end = $date_end;
    		$this->view->date_end = $date_end;
    		$params_pool[] = "date_end=".$date_end;
    	}
		
		if($params['keyword'])
    	{
    		$mod_reports->keyword = $params['keyword'];
    		$this->view->keyword = $params['keyword'];
    		$params_pool[] = "keyword=".$params['keyword'];
    	}
		
		if($params['group_id'])
    	{
    		$mod_reports->group_id = $params['group_id'];
    		$this->view->group_id = $params['group_id'];
    		$params_pool[] = "group_id=".$params['group_id'];
    	}
		
		if($params['factory_id'])
    	{
    		$mod_reports->factory_id = $params['factory_id'];
    		$this->view->factory_id = $params['factory_id'];
    		$params_pool[] = "factory_id=".$params['factory_id'];
    	}
		
		if($params['province_id'])
    	{
    		$mod_reports->province_id = $params['province_id'];
    		$this->view->province_id = $params['province_id'];
    		$params_pool[] = "province_id=".$params['province_id'];
			
			$this->view->init_cities = $mod_t_area->GetCities($params['province_id'], 2, $my_city_id_arr);
    	}
		
		if($params['city_id'])
    	{
    		$mod_reports->city_id = $params['city_id'];
    		$this->view->city_id = $params['city_id'];
    		$params_pool[] = "city_id=".$params['city_id'];
    	}
		
		if($params['sort_type'])
    	{
    		$mod_reports->sort_type = $params['sort_type'];
    		$this->view->sort_type = $params['sort_type'];
    		$params_pool[] = "sort_type=".$params['sort_type'];
    	}
		
		if(!$params['page_id'])
    	{
    		$page_id = 1;
    	}else{
    		$page_id = $params['page_id'];
    	}
    	
    	if($page_id)
    	{
    		$mod_reports->page_id = $page_id;
    		$this->view->page_id = $page_id;
    	}
		
		if($no_auth)
		{
			$this->view->data = $result = array(
				'ct' => 0,
				'total_pages' => 0,
				'current_page_info' => array()
			);
		}else{
			$this->view->data = $result = $mod_reports->HxbStatus();
		}
		
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($this->view->data);die;
		//make pagination
    	if(!empty($params_pool))
    	{
    		$params_string = implode("&", $params_pool);
    	}else{
    		$params_string = "";
    	}
    	
    	if(1 == $page_id)
    	{
    		$first_page_string = "";
    		$previous_page_string = "";
    	}else{
    		$first_page_string = "<a href='?page_id=1&".$params_string."'>最前页</a>&nbsp;&nbsp;&nbsp;";
    		$previous_page_string = "<a href='?page_id=".($page_id-1)."&".$params_string."'>上一页</a>&nbsp;&nbsp;&nbsp;";
    	}
    	
    	if($page_id < $result['total_pages'] )
    	{
    		$next_page_string = "<a href='?page_id=".($page_id+1)."&".$params_string."'>下一页</a>&nbsp;&nbsp;&nbsp;";
    		$last_page_string = "<a href='?page_id=".$result['total_pages']."&".$params_string."'>最后页</a>&nbsp;&nbsp;&nbsp;";
    	}else{
    		$next_page_string = "";
    		$last_page_string = "";
    	}
    	
		$this->view->params_string = $params_string;
    	$this->view->pagination = $first_page_string.$previous_page_string.$next_page_string.$last_page_string;
        $this->view->export_link = "/reports/hxb-status-export?".$params_string;
	}

    public function hxbStatusExportAction()
    {
        $params = $this->_request->getParams();
        $objPHPExcel = new PHPExcel();
		
		$no_auth = 0;
		
		$dealers_db = Zend_Registry::get("dealers_db");
		$select = $dealers_db->select();
		$select->from("b_bu_auth", "*");
		$select->where("emp_id = ?", $_SESSION['identity']['userid']);
		$row = $dealers_db->fetchRow($select);
		
		$mod_reports = new Db_Joins_ReportsJoins();
		$mod_reports->skip_pagination = 1;
		
		if($row)
		{
			$mod_reports->emp_auth_row = $row;
		}else{
			$no_auth = 1;
		}
    	
		if($params['date_start'])
		{
			$date_start = $params['date_start'];
		}else{
			$date_start = date("Y-m-d", mktime(1,1,1,date("m"),date("d")-1,date("Y")));
		}
    	
		if($params['date_end'])
		{
			$date_end = $params['date_end'];
		}else{
			$date_end = date("Y-m-d", mktime(1,1,1,date("m"),date("d")-1,date("Y")));
		}
		
		if($date_start)
    	{
    		$mod_reports->date_start = $date_start;
    	}
		
		if($date_end)
    	{
    		$mod_reports->date_end = $date_end;
    	}
		
		if($params['keyword'])
    	{
    		$mod_reports->keyword = $params['keyword'];
    	}
		
		if($params['group_id'])
    	{
    		$mod_reports->group_id = $params['group_id'];
    	}
		
		if($params['factory_id'])
    	{
    		$mod_reports->factory_id = $params['factory_id'];
    	}
		
		if($params['province_id'])
    	{
    		$mod_reports->province_id = $params['province_id'];
    	}
		
		if($params['city_id'])
    	{
    		$mod_reports->city_id = $params['city_id'];
    	}
		
		if($params['sort_type'])
    	{
    		$mod_reports->sort_type = $params['sort_type'];
		}else{
			$mod_reports->sort_type = 1;
		}
		
		if(!$params['page_id'])
    	{
    		$page_id = 1;
    	}else{
    		$page_id = $params['page_id'];
    	}
    	
    	if($page_id)
    	{
    		$mod_reports->page_id = $page_id;
    		$this->view->page_id = $page_id;
    	}
		
		$result = $mod_reports->HxbStatus();
		
		if($no_auth)
		{
			$result = array();
		}else{
			$result = $result['current_page_info'];
		}

        if(!empty($result))
        {
            //title
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '省份');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '城市');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '厂商');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '集团');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '经销商ID');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '汽车之家ID');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '经销商');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', '销售');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', '开通日期');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', '线索量');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1', '打开次数');

            $n = 2;
			
			if(2==$mod_reports->sort_type)
			{
				$sort_row = $result["day_active_ct"];
			}else{
				$sort_row = $result["day_leads_ct"];
			}
			
            foreach($sort_row as $id => $something)
            {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$n, isset($result["provinceName"][$id])?$result["provinceName"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, isset($result["cityName"][$id])?$result["cityName"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, isset($result["factoryName"][$id])?$result["factoryName"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, isset($result["groupName"][$id])?$result["groupName"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$n, isset($id)?$id:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$n, isset($result["_dealerId"][$id])?$result["_dealerId"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$n, isset($result["companyName"][$id])?$result["companyName"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$n, isset($result["realName"][$id])?$result["realName"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$n, isset($result["authTime"][$id])?$result["authTime"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$n, isset($result["day_leads_ct"][$id])?$result["day_leads_ct"][$id]:"");
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$n, isset($result["day_active_ct"][$id])?$result["day_active_ct"][$id]:"");

                $n += 1;
            }
        }
		
		$rd = mt_rand(1000, 9999);
		$folder = "upload/reports/";
        $file_name = "hxb-status-".date("YmdHis").$rd.".xlsx";
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($folder.$file_name);
        header("Location: /".$folder.$file_name);

        echo "End.";
        die;
    }
	
    function salesMainAction()
    {
		$params = $this->_request->getParams();
		
		$this->view->title = "日常业绩统计看板";
		
		$mod_factory = new Db_Tables2_TFactory();
		$this->view->factories = $mod_factory->GetFactories();
		
		$mod_t_area = new Db_Tables2_TArea();
		$this->view->provinces = $mod_t_area->GetProvinces();
		
		$mod_products = new Db_Tables2_BDictProduct();
		$this->view->products = $mod_products->GetProducts();
		
		$params_pool = array();
		$mod_reports = new Db_Joins_ReportsJoins();
    	
		if($params['date_start'])
		{
			$date_start = $params['date_start'];
		}else{
			$date_start = date("Y-m-d", mktime(1,1,1,date("m"),1,date("Y")));
		}
    	
		if($params['date_end'])
		{
			$date_end = $params['date_end'];
		}else{
			$date_end = date("Y-m-d");
		}
		
		if($date_start)
    	{
    		$mod_reports->date_start = $date_start;
    		$this->view->date_start = $date_start;
    		$params_pool[] = "date_start=".$date_start;
    	}
		
		if($date_end)
    	{
    		$mod_reports->date_end = $date_end;
    		$this->view->date_end = $date_end;
    		$params_pool[] = "date_end=".$date_end;
    	}
		
		if($params['keyword'])
    	{
    		$mod_reports->keyword = $params['keyword'];
    		$this->view->keyword = $params['keyword'];
    		$params_pool[] = "keyword=".$params['keyword'];
    	}
		
		if($params['factory_id'])
    	{
    		$mod_reports->factory_id = $params['factory_id'];
    		$this->view->factory_id = $params['factory_id'];
    		$params_pool[] = "factory_id=".$params['factory_id'];
    	}
		
		if($params['product_id'])
    	{
    		$mod_reports->product_id = $params['product_id'];
    		$this->view->product_id = $params['product_id'];
    		$params_pool[] = "product_id=".$params['product_id'];
    	}
		
		if($params['province_id'])
    	{
    		$mod_reports->province_id = $params['province_id'];
    		$this->view->province_id = $params['province_id'];
    		$params_pool[] = "province_id=".$params['province_id'];
			
			$this->view->init_cities = $mod_t_area->GetCities($params['province_id']);
    	}
		
		if($params['city_id'])
    	{
    		$mod_reports->city_id = $params['city_id'];
    		$this->view->city_id = $params['city_id'];
    		$params_pool[] = "city_id=".$params['city_id'];
    	}
		
		$this->view->data = $result = $mod_reports->SalesMain();
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($this->view->data);die;
		
    	if(!empty($params_pool))
    	{
    		$params_string = implode("&", $params_pool);
    	}else{
    		$params_string = "";
    	}
    	
		$this->view->params_string = $params_string;
        $this->view->export_link = "/reports/sales-main-export?".$params_string;
	}

    public function salesMainExportAction()
    {
        $params = $this->_request->getParams();
        $objPHPExcel = new PHPExcel();
		
		$mod_reports = new Db_Joins_ReportsJoins();
		$mod_t_area = new Db_Tables2_TArea();
    	
		if($params['date_start'])
		{
			$date_start = $params['date_start'];
		}else{
			$date_start = date("Y-m-d", mktime(1,1,1,date("m"),1,date("Y")));
		}
    	
		if($params['date_end'])
		{
			$date_end = $params['date_end'];
		}else{
			$date_end = date("Y-m-d");
		}
		
		if($date_start)
    	{
    		$mod_reports->date_start = $date_start;
    		$this->view->date_start = $date_start;
    		$params_pool[] = "date_start=".$date_start;
    	}
		
		if($date_end)
    	{
    		$mod_reports->date_end = $date_end;
    		$this->view->date_end = $date_end;
    		$params_pool[] = "date_end=".$date_end;
    	}
		
		if($params['keyword'])
    	{
    		$mod_reports->keyword = $params['keyword'];
    		$this->view->keyword = $params['keyword'];
    		$params_pool[] = "keyword=".$params['keyword'];
    	}
		
		if($params['factory_id'])
    	{
    		$mod_reports->factory_id = $params['factory_id'];
    		$this->view->factory_id = $params['factory_id'];
    		$params_pool[] = "factory_id=".$params['factory_id'];
    	}
		
		if($params['product_id'])
    	{
    		$mod_reports->product_id = $params['product_id'];
    		$this->view->product_id = $params['product_id'];
    		$params_pool[] = "product_id=".$params['product_id'];
    	}
		
		if($params['province_id'])
    	{
    		$mod_reports->province_id = $params['province_id'];
    		$this->view->province_id = $params['province_id'];
    		$params_pool[] = "province_id=".$params['province_id'];
			
			$this->view->init_cities = $mod_t_area->GetCities($params['province_id']);
    	}
		
		if($params['city_id'])
    	{
    		$mod_reports->city_id = $params['city_id'];
    		$this->view->city_id = $params['city_id'];
    		$params_pool[] = "city_id=".$params['city_id'];
    	}
		
		$result = $mod_reports->SalesMain();

        if(!empty($result))
        {
            //title
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '销售');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '签单额');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '发布额');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '回款额');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '欠款额');

            $n = 2;
			
			$qiandan = $fabu = $huikuan = $qiankuan = 0;
			
            foreach($result as $data)
            {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$n, $data["emp_name"]);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, $data["qiandan"]);$qiandan += $data["qiandan"];
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, $data["fabu"]);$fabu += $data["fabu"];
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, $data["huikuan"]);$huikuan += $data["huikuan"];
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$n, $data["qiankuan"]);$qiankuan += $data["qiankuan"];

                $n += 1;
            }
			
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$n, "汇总");
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, $qiandan);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, $fabu);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, $huikuan);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$n, $qiankuan);
        }
		
		$rd = mt_rand(1000, 9999);
		$folder = "upload/reports/";
        $file_name = "sales-main-".date("YmdHis").$rd.".xlsx";
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($folder.$file_name);
        header("Location: /".$folder.$file_name);

        echo "End.";
        die;
    }
	
    function cityListAction()
    {
		$params = $this->_request->getParams();
		
		$this->view->title = "汇销宝城市列表";
		
		$dealers_db = Zend_Registry::get("dealers_db");
		$select = $dealers_db->select();
		$select->from("b_bu_auth", "*");
		$select->where("emp_id = ?", $_SESSION['identity']['userid']);
		$row = $dealers_db->fetchRow($select);
		
		if($row)
		{
			$this->view->my_group_id_arr = explode("|", $row['group_id_txt']);
			$this->view->my_factory_id_arr = explode("|", $row['factory_id_txt']);
			$my_city_id_arr = explode("|", $row['city_id_txt']);
			
			if(!$my_city_id_arr[0])
			{
				$my_city_id_arr = NULL;
			}
		}
		
		$mod_t_area = new Db_Tables2_TArea();
		$this->view->provinces = $mod_t_area->GetProvinces($my_city_id_arr);
		
		$params_pool = array();
		$mod_reports = new Db_Joins_ReportsJoins();
		$mod_reports->emp_auth_row = $row;
    	
		if($params['province_id'])
    	{
    		$mod_reports->province_id = $params['province_id'];
    		$this->view->province_id = $params['province_id'];
    		$params_pool[] = "province_id=".$params['province_id'];
			
			$this->view->init_cities = $mod_t_area->GetCities($params['province_id'], 2, $my_city_id_arr);
    	}
		
		if($params['city_id'])
    	{
    		$mod_reports->city_id = $params['city_id'];
    		$this->view->city_id = $params['city_id'];
    		$params_pool[] = "city_id=".$params['city_id'];
    	}
		
		if(!$params['page_id'])
    	{
    		$page_id = 1;
    	}else{
    		$page_id = $params['page_id'];
    	}
    	
    	if($page_id)
    	{
    		$mod_reports->page_id = $page_id;
    		$this->view->page_id = $page_id;
    	}
		
		$this->view->data = $result = $mod_reports->CityList();
		
		//make pagination
    	if(!empty($params_pool))
    	{
    		$params_string = implode("&", $params_pool);
    	}else{
    		$params_string = "";
    	}
    	
    	if(1 == $page_id)
    	{
    		$first_page_string = "";
    		$previous_page_string = "";
    	}else{
    		$first_page_string = "<a href='?page_id=1&".$params_string."'>最前页</a>&nbsp;&nbsp;&nbsp;";
    		$previous_page_string = "<a href='?page_id=".($page_id-1)."&".$params_string."'>上一页</a>&nbsp;&nbsp;&nbsp;";
    	}
    	
    	if($page_id < $result['total_pages'] )
    	{
    		$next_page_string = "<a href='?page_id=".($page_id+1)."&".$params_string."'>下一页</a>&nbsp;&nbsp;&nbsp;";
    		$last_page_string = "<a href='?page_id=".$result['total_pages']."&".$params_string."'>最后页</a>&nbsp;&nbsp;&nbsp;";
    	}else{
    		$next_page_string = "";
    		$last_page_string = "";
    	}
    	
		$this->view->params_string = $params_string;
    	$this->view->pagination = $first_page_string.$previous_page_string.$next_page_string.$last_page_string;
        $this->view->export_link = "/reports/city-list-export?".$params_string;
	}

    public function cityListExportAction()
    {
        $params = $this->_request->getParams();
        $objPHPExcel = new PHPExcel();
		
		$dealers_db = Zend_Registry::get("dealers_db");
		$select = $dealers_db->select();
		$select->from("b_bu_auth", "*");
		$select->where("emp_id = ?", $_SESSION['identity']['userid']);
		$row = $dealers_db->fetchRow($select);
		
		$mod_reports = new Db_Joins_ReportsJoins();
		$mod_reports->skip_pagination = 1;
		$mod_reports->emp_auth_row = $row;
    	
		if($params['province_id'])
    	{
    		$mod_reports->province_id = $params['province_id'];
    	}
		
		if($params['city_id'])
    	{
    		$mod_reports->city_id = $params['city_id'];
    	}
		
		if(!$params['page_id'])
    	{
    		$page_id = 1;
    	}else{
    		$page_id = $params['page_id'];
    	}
    	
    	if($page_id)
    	{
    		$mod_reports->page_id = $page_id;
    		$this->view->page_id = $page_id;
    	}
		
		$result = $mod_reports->CityList();
		
        $result = $result['current_page_info'];

        if(!empty($result))
        {
            //title
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '城市');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '经销商数量');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '汇销宝开通量');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '汇销宝开通率');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '活跃率');

            $n = 2;
			
            foreach($result as $city_id => $info)
            {
				if($info['dealer_total'])
				{
					$num_a = round($info['hxb_total']/$info['dealer_total']*100,0)."%";
				}else{
					$num_a = "-";
				}
				
				if($info['hxb_total'])
				{
					$num_b = round($info['hxb_active']/$info['hxb_total']*100,0)."%";
				}else{
					$num_b = "-";
				}
				
				
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$n, $info['city_name']);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, $info['dealer_total']);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, $info['hxb_total']);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, $num_a);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$n, $num_b);

                $n += 1;
            }
        }
		
		$rd = mt_rand(1000, 9999);
		$folder = "upload/reports/";
        $file_name = "city-list-".date("YmdHis").$rd.".xlsx";
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($folder.$file_name);
        header("Location: /".$folder.$file_name);

        echo "End.";
        die;
    }
	
	 //模板导出
    public function templateExportAction()
    {
        $objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '经销商名称');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '集团名称');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '集团ID');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '商家ID');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '省份');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '城市');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '授权厂商');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', 'APP活跃度平均分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', '推荐名额使用率平均分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', '有效车系模板文章覆盖度平均分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1', '400接起率平均分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L1', '400呼叫等待时长平均分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M1', '商擎总分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N1', '私有线索');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O1', '车商汇-分配销售线索总量');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P1', '车商汇-抢公共网上订单量');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q1', '车商汇-400来电量(公共)');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R1', '产品库实拍图加分');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S1', '车商汇-PV');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T1', '车商汇-UV');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U1', '车商汇-自发文章数量');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V1', '车商汇-自发文章PV');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W1', '车商汇-文章推荐数');
//			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X1', '创建时间');

		$rd = mt_rand(1000, 9999);
		$folder = "upload/reports/";
		$file_name = "template" . date("YmdHis") . $rd . ".xls";
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save($folder . $file_name);

		header("Location: /" . $folder . $file_name);

        echo "End.";
        die;
    }
	
	 //模板导出
    public function mainbrandTemplateExportAction()
    {
        $objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '经销商名称');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '经销商ID');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '主要厂商名称');

		$rd = mt_rand(1000, 9999);
		$folder = "upload/reports/";
		$file_name = "template" . date("YmdHis") . $rd . ".xls";
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save($folder . $file_name);

		header("Location: /" . $folder . $file_name);

        echo "End.";
        die;
    }
	
}